DOWNLOAD Sample Code

Database-Based Authentication for PHP Apps, Part 1

Learn how to secure PHP-based Web applications via database-based authentication.

Published May 2007

Managing secure access to Web pages and applications is a common problem. You want to enable those you trust to access data while preventing unauthorized ones from gaining access to it. In most cases, database-based authentication is the solution.

Authentication systems contain an Access Control List (ACL) that lists your user credentials and matches them to your assigned system privileges. Credentials are typically a user name and password pair. Credentials link your users to system privileges. System privileges let your accounts access or modify data, and enable your accounts to execute subsystems or subroutines. Your accounts can be users, groups, or systems.

In this article, you will learn how to implement this concept in PHP-based Web applications. You will learn how to design and implement an authentication database model, and plan and manage all aspects of user interactions in a browser-hosted application.

In Part 2 of this article, you will explore different methods of database access and how authentication systems can leverage security policies in an Oracle Virtual Private Database (VPD), as well as with the built-in DBMS_APPLICATION_INFO package.

Architecture, Authentication, and Encryption

Architecture. When Web applications request information from the Apache HTTP server, they send information from the client to the server. Users view this process as submitting a URL and receiving a Web page in reply. A URL is only the exposed part of the URI; the URI contains HTTP headers, cookies, and a URL. The information is transmitted as an encoded name and value pair in the URI.

Cookies are small text files containing clear or encrypted text. Cookies contain the current transaction state of communication between the browser and server application. The content of cookies was once frequently attached to the URI to help maintain transaction state. A single session cookie now sends a numeric reference that enables the prior name and value cookie pairs to be stored securely on the server. The numeric reference is known as a Web session ID.

When using Web application sessions, the session ID and expiration information about the session are sent as a special session cookie. When client browsers disallow cookies, Web applications may default to URL rewriting, which redirects the session cookie from the undisclosed URI to the disclosed URL. You recognize two benefits by leaving the data on the server and storing it by a unique session ID — you minimize the size of the URL, and you secure the internal details of user interactions.

Although keeping session information on the server is more secure, you must guard against session hijacking. A malicious user can hijack a session by (a) stealing a session cookie, (b) copying a URL that includes the session ID or (c) snatching it through a man-in-the-middle attack. Modern browsers make this more difficult, and PHP does not write the physical session cookies to a file system.

A benefit of the cookie and session architecture is that you can mine historical session data, provided you store data in a normalized data model. The data let you discover how customers use your Web applications. The data also let you discover customer browsing patterns and purchasing trends. This type of information enables businesses to target marketing to individuals as opposed to groups.

Authentication. You will use one of two authentication models to verify users in your Web applications. Your choice is between the basic HTTP/HTTPS authentication, and the cookie and session authentication models. A third authentication model, a digest HTTP authentication, is under development but appears likely to remain an incomplete solution for a few more years.

Both of the first two authentication models let you take credentials from a Web page and validate them against an ACL. They also let you work successfully whether or not the browser accepts cookies. The basic HTTP/HTTPS and digest HTTP methods validate against a realm without using a cookie, while the session management writes at least a session ID cookie.

URL rewriting presents some security risks when you send the session ID as part of the URL because some users instant message the URL to someone else. That other person may hijack the authorization to access or compromise confidential data. An alternative to URL rewriting is placing the session ID into the rendered Web page as a hidden field. While this does create vulnerability, it is less risky than appending the session to the URL.

Your PHP authentication script takes a name and value pair for the user name and password. The script then compares the pair against data stored in your ACL. This is the same process in either the discussed authentication models. While it is customary to store server-side passwords in encrypted forms, you should recognize that any compromise of the clear text user and password values compromises your system security.

Therefore, you should not force users to create complex passwords because they aren't easily remembered. Users write hard-to-remember passwords on notepads or yellow sticky pads. Written passwords are security risks to your Web application because hackers might find and misuse them.

Encryption. Encryption is always a hot topic in Web application development, deployment, and management.

There are two encryption techniques in PHP Web applications. The first involves encrypting your protocol. Encrypted protocols, like HTTPS, protect you from man-in-the-middle attacks using network analyzers, commonly known as packet sniffers. The second involves encrypting your user password from exposure to prying eyes on the server.

Password encryption protects your system from attacks best when the encrypted passwords are stored separately from the source code. When the ACL is a file on the file system, both the method of encryption and encrypted values can be compromised by a single server hack. You reduce the risk of compromising the encrypted passwords by storing your ACL in a database.

Identity Management Data Modeling

Building an identity management data model can vary from the simple to the complex. The simplest model is a single table containing the ACL for your application. Unfortunately, this model only works with basic HTTP/HTTPS authentication.

The simplest way to implement cookie and session authentication requires at least two tables. One table contains the ACL, and the other contains the session data. The following depicts a basic model:

Figure 1 Basic authentication data model

A more effective solution for authentication would support capturing and mining user interactions. These vary from the list of Web pages called to individual events, like mouse clicks. Figure 2 shows a data model that can capture user navigation and both successful and unsuccessful logins. For our example, this data model is created by the create_identity_db.sql script (see sample code zip).

Figure 2 Detailed authentication data model

The larger model lets you track versions of defined modules against runtime calls to modules. Actual parameters map to catalog items, like books on Amazon.com. An access log is added to track multiple connections made during a single session. The full details of how you implement your model can vary based on your goals. Figure 2 also shows an example model that supports version control in a catalog ordering application. At present, the sample code only captures invalid log in attempts and the ACCESS_LOG table is renamed to reflect that difference as INVALID_SESSION table.

Authentication Process Models

Basic HTTP/HTTPS authentication operates by establishing a browser's credentials in a realm. Browsers can concurrently support many realms. Realms are set in the header of XHTML documents. They can be manually coded in the form, or set in the AuthName directive of the httpd.conf file. Realms act as guards over protected server areas, and they can apply to one or more protected server areas. One advantage of basic HTTP/HTTPS authentication is that you don't have to code a login or logout facility. Browsers provide the login forms and you logout by closing all open browser windows.

A major downfall of the basic HTTP/HTTPS authentication method is you must close all browser windows to end active realm authorizations. This means that a user can authenticate, walk away from the terminal while leaving an open browser, and enable someone to compromise their confidential data by hijacking their identity.

Cookie and session authentication operates by checking user credentials against your ACL. When the ACL is stored in a file, this means reading the file and comparing the name value pairs of a user name and password against a submitted clear text user name and encrypted password.

When the ACL is stored in a database, this means one thing on initial login and another on subsequent connections. On initial login, you connect to the database, read the user name and encrypted password values from a database table; and compare the results against the submitted clear text user name and encrypted password. On subsequent Web requests, you connect to the database, read the session ID, and compare results against the submitted session ID. Unlike basic HTTP/HTTPS authentication, you can successfully logout while leaving the Web browser open.

While the session ID becomes a potential security risk because of session hijacking, some reasonable precautions can minimize this risk. The biggest security gap can be closed by implementing your login page as a PHP script instead of a simple text XHTML form. The sample cookie and session code demonstrates this approach. It ensures that a second user on a shared machine doesn't gain entry from the prior credentials. The implementation details are found in the following Cookie and Session Authentication Model section.

Basic HTTP/HTTPS Authentication Model. The basic HTTP/HTTPS authentication model authorizes once for a realm and revokes the realm authentication only when all browser windows are closed. The activity diagram representing the model from within the browser context is shown in Figure 3. Microsoft Internet Explorer provides three attempts before failing authentication but you can simply refresh the page to get another three successive attempts. The Firefox browser differs by prompting until the user cancels the authentication process.

Figure 3 Basic HTTP/HTTPS authentication activity diagram

You start a Web application session by entering a URL for the login form, which then posts to the server and sends a message back to the browser requesting that it collect and send for validation the user's credentials. When you submit the credentials to the SignOnDB.php program, the code attempts to validate your log credentials. When it works you can then use any Web pages in the realm. Authentication failure re-prompts with the browser login form as noted previously.

When implementing basic HTTP/HTTPS authentication, PHP uses three predefined variable names in the $_SERVER array. Basic HTTP/HTTPS authentication puts the user ID and the password in the $_SERVER['PHP_AUTH_USER'] and $_SERVER['PHP_AUTH_PW'] respectively. When the third, HTTP_AUTHORIZATION, is not set, you submit these two $_SERVER values to your authentication function. After your server-side program authenticates the user, subsequent reads of the $_SERVER array HTTP_AUTHORIZATION name let the browser access to the realm until it is closed.

An easy way to protect your pages in the basic HTTP/HTTPS authentication model is to include a Boolean control variable that is false until authentication is verified. You display content only when the Boolean control variable becomes true. Your code should check for authentication, and prompt for credentials when you are not authenticated in the realm. This code should be found in all pages on your secure Web site.

You trigger HTTP header validation in your server-side script by checking the values of these predefined $_SERVER variables. The request to the SignOnRealm.php page places these values in the returned header, which instructs the browser to prompt with a credential entry dialog box. Clicking the credentials OK button sends a new request to the server-side script. The server-side script then attempts to validate the submitted credentials unless a user cancels the process. These realm sign in scripts typically return a failure message when the user cancels the process. Some sites also shut user accounts when there are three or four consecutive authentication failures because someone may be attempting to hack into the system through a known user name.

Setup Test Application. The demonstration application uses an Oracle schema named IDMGMT1, and the password is the same as the schema. You can create the user and environment by following these steps:

1. Log into the database as the privileged SYSTEM user and run the following commands:

SQL> CREATE USER IDMGMT1 IDENTIFIED BY IDMGMT1;
SQL> GRANT CONNECT, RESOURCE TO IDMGMT1;

2. Connect to the new user schema:

SQL> CONNECT IDMGMT1/IDMGMT1@XE

3. Run the create_identity_db1.sql script in the IDMGMT1 schema to create all necessary objects and seed the SYSTEM_USER table:

SQL> @create_identity_db1.sql

4. Place the following files in your htdocs directory or a subdirectory of the htdocs directory:

 • SignOnRealm.php
 • SignOnDB.php
 • AddDbUser.php

These steps complete the setup required for our purposes here. You can now experiment with either the realm or session identification management examples.

Experiment with Basic HTTP/HTTPS Authentication.

(This part was deleted by Peter Ping Liu for PHP/Oracle class. The purpose is to enable students to focus on the database authentication part.)

Analyze the Basic HTTP/HTTPS Authentication Code. After you enter your user name and password, your script will call the verify_db_login() function. The following PHP code demonstrates how you begin the authentication of users in your realm:

// Declare control variable.
$valid_user = false;

// Authenticate user.
if ((isset($_SERVER['PHP_AUTH_USER'])) && (isset($_SERVER['PHP_AUTH_PW'])))
  if (verify_db_login($_SERVER['PHP_AUTH_USER'],$_SERVER['PHP_AUTH_PW']))
    $valid_user = true;

This function opens a connection to the Oracle database and checks whether the credentials are valid. The verify_db_login() function returns true when credentials are valid and false when not. When the SYSTEM_USER table is missing, the code tells you to check for the missing table. The main code for the Web page is shown in the function:

// Check for authorized account.
function verify_db_login($userid,$passwd)
{
  // Attempt connection.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Return a row.
    $s = oci_parse($c,"SELECT   NULL
                       FROM     system_user
                       WHERE    system_user_name = :userid
                       AND      system_user_password = :passwd
                       AND      SYSDATE BETWEEN start_date
                                        AND NVL(end_date,SYSDATE)");

    // Encrypt password.
    $newpassword = sha1($passwd);
    // Bind variables as strings.
    oci_bind_by_name($s,":userid",$userid);
    oci_bind_by_name($s,":passwd", $newpassword));

      // Execute the query.
      if (@oci_execute($s,OCI_DEFAULT))
      {
        // Check for a validated user, also known as a fetched row.
        if (oci_fetch($s))
           return true;
        else
          return false;
      }
      else
      {
        // Print error when execution fails.
        $errorMessage = "Check for a missing SYSTEM_USER table.<br />";
        print $errorMessage;
      }

    // Close connection.
    oci_close($c);
  }
  else
  {
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
}

There are two things to note about the verify_db_function() function. First, the query returns a null value, which eliminates processing any unnecessary return values. Second, the clear text password is encrypted using the sha1() function and assigned to a local variable.

You now know how to build the required components for basic HTTP/HTTPS authentication. The two caveats associated with this authentication model are that you: (1) have complete access to the realm once you authenticate your credentials in a browser; and (2) can only log out by closing all browser windows.

Cookie and Session Model. The cookie and session model is popular because you can let users login and logout of Web applications without requiring them to shutdown all of their browser windows. This authentication model is also more complex than the basic HTTP/HTTPS model. There are different ways that you can implement cookie and session authentications.

The login and logout operations operate inside active browsers. This operating context adds more complexity to the cookie and session model. For example, the browser backward, forward, and refresh buttons require special handling in your code. A refresh button shouldn't attempt to send a new user authentication more than once. Likewise, a refresh button should not trigger new authentication requests after a user has logged out of an application. Unfortunately, they do unless the program prevents it.

Calling a login PHP script, not an XHTML Web page is the most direct way to guarantee a complete logout operation in your Web application. A PHP script can reset the session value whereas an XHTML Web page cannot because it passes the prior session value when submitting missing or new credentials to a target page.

There are several predefined variables in PHP that you can use to store globally scoped variables. One is the $_SESSION array. It lets you add any name-value pair, where the name becomes the index value to the actual data value. The advantages of using the $_SESSION array are simplicity and flexibility but the disadvantages can involve conflicts with other libraries.

The session_start() function returns the active PHP session ID value. You must call the session_start() function before the session_regenerate_id() function because it actually calls the session_destroy() function. The session_destroy() depends on the existence of a PHP session ID and will raise an error when one doesn't exist before you call it. Your call to the session_regenerate_id() function with an actual true parameter will reset the PHP session ID. You use runtime error suppression to guarantee that calling the session_regenerate_id() function doesn't raise an error when no prior session exists in the context of the browser.

<?php
  // Start and regenerate session.
  session_start();
  @session_regenerate_id(true);
  $_SESSION['sessionid'] = session_id();
?>

The preceding scriptlet alters the session value in the browser, and it will force new authentication with the next click of the Login button. It does this by replacing the previously authenticated PHP session ID with a new session ID. The new PHP session ID is transmitted as part of the URL to the next form.

There are two ways to manage database connections once you establish your PHP session: One uses a persistent connection between the PHP module and the database and the other uses a non-persistent connection between the two. Persistent connections let you start a transaction scope that can span several Web requests, and they end only when you issue a database commit command. During the scope of a persistent connection, you can run SQL and PL/SQL statements that lock rows pending change or after an uncommitted change. Non-persistent connections let you run SQL or PL/SQL statements to query or change data between opening and closing a connection, which occurs in a single Web request. Data transactions limited by a single connection are also known as autonomous transactions because their duration is limited to the duration of the connection.

These sample programs use non-persistent database connections to query and transact against the Oracle database. Consequently, all database transactions perform as autonomous transactions.

The SignOnDB.php Web page, shown in Figure 5, implements this type of PHP scriptlet before the XHTML page. The XHTML rendering found in the SignOnDB.php form is also found in the AddDbUser.php page as the signOnForm() function.

You will put the PHP authentication logic in all Web pages called after a user logs into the application. At present all programming logic is in the AddDbUser.php page, shown in Figure 6, but you can move the twelve functions into one authentication library.

Your browser must accept cookies for these programs to work. When cookies are disabled, all you can do is login, logout, and fail to enter a new user because the session values are lost between pages.

Experiment with Session Authentication. You can experiment with the session identity management by following these steps:

1. Open the session identification management examples by using the following URL, assuming that you placed the code in the htdocs directory:

http://localhost/SignOnDB.php 

2. You can use either of the seeded accounts presented in the Basic HTTP/HTTPS authentication:

Figure 6 Cookie and Session Login page

3. You can now enter a new user into your credential repository in the New User form, which requires user name to be a string that is 6 to 10 characters long and starts with a letter:

Figure 7 Cookie and session Add New User page

4. After you add the new user, the AddDBUser.php script will present you with a new copy of the New User form. It will acknowledge whether you successfully added a new user or explain what rule the entry violated. You can connect to the IDMGMT1 schema and run the following query to inspect a new user entry:

SELECT system_user_name, system_user_password FROM system_user; 

Analyze the Session Authentication Code

The PHP program's logic accounts for the typical nuances of the forward, backward and refresh browser buttons. Figure 7 displays the rendered Web page. When you call or refresh the form by using the browser navigation buttons, the program determines whether (a) the session is registered in the database model to the current user, (b) the remote address is the same client IP address from authentication, and (c) the session is current. A session is current when the last related database activity of the session occurred within the last five minutes.

Both the initial sign on form and add user forms check and verify a registered session before the program logic looks for new user credentials and attempts to authenticate the user. When the credentials are authenticated, the SignOnDB.php script registers the new session and calls the AddDbUser.php script. A refresh of the AddDbUser.php script calls the signOnForm() function that mimics the SignOn.php form's behavior. When the AddDbUser.php script authenticates credentials, it renders a page using the addUserForm() and when either script repudiates credentials, they log a failed login attempt and render a new sign on page.

Figure 8 Cookie and session authentication activity diagram

The Web page contains two XHTML forms. One form lets you enter a new user, validate that the credentials meet guidelines (e.g., 6 to 10 character strings starting with a letter), and inserts the credentials into the database. The other form lets you opt to log out of the application by calling the SignOnDB.php script, which resets the session to prevent it appearing authenticated in the database after a user logged out.

The Web page starts the session and assigns it to the $_SESSION array, assigns any submitted credentials to local variables, and then checks to see whether a current authenticated session is available. This is done by the following code:

  // Check for valid session and regenerate when session is invalid.
  if ((get_session($_SESSION['sessionid'],$userid,$passwd) == 0) ||
      (($_SESSION['userid'] != $userid) && ($userid)))
  {
    // Regenerate session ID.
    session_regenerate_id(true);
    $_SESSION['sessionid'] = session_id();
  }
  else
  {
    $authenticated = true;
  }

The get_session() function opens a connection to the database and queries a result set from the join of the SYSTEM_USER and SYSTEM_SESSION tables. The results from the query determine whether or not the session is authenticated. Part of this verification process checks to make sure that the request originates from the same IP address. When authenticated, Web page variables are assigned values from the query to process the page request. At the same time, the LAST_UPDATE_DATE column timestamp is updated by the update_session() function. The attempt is logged by the record_session() function to the OBSOLETE_SESSION table in the database when authentication fails.

// Get a valid session.
  function get_session($sessionid,$userid = null,$passwd = null)
  {
    // Attempt connection and evaluate password.
    if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
    {
      // Assign metadata to local variable.
      $remote_address = $_SERVER['REMOTE_ADDR'];

      // Return database UID within 5 minutes of session registration.
      // The Oracle DATE data type is a timestamp where .003472222 is
      // equal to 5 minutes.
      $s = oci_parse($c,"SELECT   su.system_user_name
                         ,        ss.system_remote_address
                         ,        ss.system_session_id
                         FROM     system_user su JOIN system_session ss
                         ON       su.system_user_id = ss.system_user_id
                         WHERE    ss.system_session_number = :sessionid
                         AND     (SYSDATE - ss.last_update_date) <=
                                    .003472222");

      // Bind the variables as strings.
      oci_bind_by_name($s,":sessionid",$sessionid);

      // Execute the query, error handling should be added.
      if (@oci_execute($s,OCI_DEFAULT))
      {
        // Check for a validated user, also known as a fetched row.
        if (oci_fetch($s))
        {
           // Assign unqualified values.
          $_SESSION['userid'] = oci_result($s,'SYSTEM_USER_NAME');

          // Check for same remote address.
          if ($remote_address == oci_result($s,'SYSTEM_REMOTE_ADDRESS'))
          {
            // Refresh last update timestamp of session.
            update_session($c,$sessionid,$remote_address);
            return (int) oci_result($s,'SYSTEM_SESSION_ID');           }
           else
           {
             // Log attempted entry.
             record_session($c,$sessionid);
             return 0;
           }
        }
        else
        {
          // Record when not first login.
          if (!isset($userid) && !isset($passwd))
            record_session($c,$sessionid);

          // Return a zero.
          return 0;
        }
      else
      {
        // Print error when oci_execute() fails.
        $errorMessage = "Check for a missing SYSTEM_USER or ";
        $errorMessage .= "SYSTEM_SESSION tables.<br />";
        print $errorMessage;
        return 0;
      }

      // Close the connection.
      oci_close($c);
    }
    else
    {
      $errorMessage = oci_error();
      print htmlentities($errorMessage['message'])."<br />";
      return 0;
    }
  }

You should note that the functions called from the get_session() function share the database connection opened to confirm an existing session. This goal is accomplished by passing the connection as an actual parameter to the other two functions. Managing the database transaction scope in a single database connection lets you manage multiple SQL and PL/SQL statements as components of a transaction. This enables functions to contain single subjects of your data abstraction layer and lets you treat nested function calls as subroutines.

Figure 9 Cookie and session Add New User Bad Credential page

The next section of primary code in the Web page renders different versions of the form because it serves multiple purposes. Figure 6 shows the form rendered on initial login to the application. Figure 9 demonstrates the form rendered after attempting to enter a null user name. The code calls the create_new_db_user() function when appropriate to add new users to the ACL in the SYSTEM_USER table, as shown:

  // Check whether the program should:
  // -----------------------------------------------------------------
  //  Action #1: Verify new credentials and start a database session.
  //  Action #2: Continue a session on refresh button.
  //  Action #3: Provide a new form after adding a user.
  //  Action #4: Provide a new form after failing to add a user.
  // -----------------------------------------------------------------
  if (($authenticated) || (authenticate($userid,$passwd)))
  {
    // Assign inputs to variables.
    $newuserid = @$_POST['newuserid'];
    $newpasswd = @$_POST['newpasswd'];

    // Set message and write new credentials.
    if ((isset($newuserid)) && (isset($newpasswd)) &&
        (($code = verify_credentials($newuserid,$newpasswd)) !== 0))
    {
      // Render empty form with error message from prior attempt.
      addUserForm(array("code"=>$code
                       ,"form"=>"AddDbUser.php"
                       ,"userid"=>$newuserid));
    }
    else
    {
      // Create new user only when authenticated.
      if (!(isset($userid)) && (isset($_SESSION['userid'])))
       create_new_db_user($_SESSION['db_userid'],$newuserid,$newpasswd);

      // Render fresh empty form.
      addUserForm(array("form"=>"AddDbUser.php"));
    }
  }
  else
  {
    // Destroy the session and force re-authentication.
    session_destroy();

    // Redirect to the login form.
    signOnForm();
  }

The nested if statement filters calls to the create_new_db_user() function by ensuring that $newuserid and $newpasswd variables are set. These two variables can only be set in the AddDbUser.php Web page, which means these cannot be called until after your user has authenticated. This works on a subsequent call to the AddDbUser.php Web page because clicking the Add User button recursively calls the same Web page.

  // Add a new user to the authorized control list.
  function create_new_db_user($userid,$newuserid,$newpasswd)
  {
    // Attempt connection and evaluate password.
    if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
    {
      // Check for prior insert, possible on Web page refresh.
      if (!is_inserted($c,$newuserid))
      {
        // Encrypt password.
        $newpassword = sha1($passwd);
        
        // Return database UID.
        $s = oci_parse($c,"INSERT INTO system_user
                           ( system_user_id
                           , system_user_name
                           , system_user_password
                           , system_user_group_id
                           , system_user_type
                           , created_by
                           , creation_date
                           , last_updated_by
                           , last_update_date )
                           VALUES
                           ( system_user_s1.nextval
                           , :newuserid
                           , :newpasswd
                           , 1
                           , 1
                           , :userid1
                           , SYSDATE
                           , :userid2
                           , SYSDATE)");

        // Bind the variables as strings.
        oci_bind_by_name($s,":newuserid",$newuserid);
        oci_bind_by_name($s,":newpasswd", $newpassword);
        oci_bind_by_name($s,":userid1",$userid);
        oci_bind_by_name($s,":userid2",$userid);

        // Execute the query, error handling should be added.
        if (!@oci_execute($s,OCI_COMMIT_ON_SUCCESS))
        {
          // Print error when oci_execute() fails.
          $errorMessage = "Check for a missing SYSTEM_USER table.<br />";
          print $errorMessage;
        }
      }

      // Close the connection.
      oci_close($c);

    }
    else
    {
      $errorMessage = oci_error();
      print htmlentities($errorMessage['message'])."<br />";
    }
  }

Inside of the create_new_db_user() function, the function makes a call to the is_inserted() function. This checks to make sure that the user does not already exist before attempting to insert one into the SYSTEM_USER table. Like the earlier nested function examples, the is_inserted() function shares the local connection for transaction control purposes. Also, the sha1() function converts the clear text password to an encrypted string before binding the user password to the data manipulation variable.

After successfully inserting the new user, the New User form renders itself again waiting for you to insert another user or to click the Log Out button. The Log Out button returns you to the login screen, which resets your session identifier.

Conclusion

Now that you have learned how identity management works and how you can implement a basic identity management solution, you should be comfortable with the terminology, architecture, and approach to authenticating your users.

You can now manage user authentication and access equally, but all users are not equal. Some may have unrestricted access, while most others have restricted access privileges. Part 2 of this article shows you how to link identity management with two technologies that enable fine-grained access control.

Although the VPD feature is the "state of the art," the older technology, DBMS_APPLICATION_INFO, also works in Oracle8i, Oracle9i, and Oracle Database 10g Release 1. It is also the core utility supporting Oracle E-Business 11i Suite authentication. Both these technologies let you implement fine grained access privileges and roles.

Go on to Part 2